const express = require('express');
const router = express.Router();
const authenticator = require('../middleware/authenticator');
const Excel = require('exceljs');



function bookRouteModule(db) {

    //得到图书列表  可传参数，当前页码，当前展示数量  默认为1，10
    router.get('/list', async (req, res) => {
        const page = parseInt(req.query.page) || 1;
        const size = parseInt(req.query.size) || 10;

        const offset = (page - 1) * size;

        try {
            const [totalResult] = await db.query('SELECT COUNT(*) as total FROM book');

            // 得到当前图书列表里的总数
            const total = totalResult[0].total;

            const [rows] = await db.query('SELECT * FROM book LIMIT ?,?', [offset, size]);

            res.json({
                code: 200,
                message: "ok",
                data: {
                    count: total,
                    list: rows
                }
            })

            console.log(totalResult, "totalResult");

        } catch (error) {
            console.log(error, "错误");
            res.status(500).send({ code: 500, msg: '服务器错误,请稍后重试' });
        }
    })

    //添加图书
    router.post('/add', authenticator, async (req, res) => {
        const { Title, Author, ISBN, Publisher, PublishDate, Category, Stock } = req.body;
        const query = 'INSERT INTO book ( Title, Author, ISBN, Publisher, PublishDate, Category, Stock) VALUES ( ?, ?, ?, ?, ?, ?, ?)';
        const values = [Title, Author, ISBN, Publisher, PublishDate, Category, Stock];
        try {
            const [result] = await db.query(query, values); // 返回插入的行数
            console.log(result, "result");
            res.json({
                code: 200,
                message: "图书添加成功！"
            })
        } catch (error) {
            console.log(error, "错误");
            res.status(500).send({ code: 500, msg: '图书添加失败,请稍后重试' });

        }
    })

    router.get("/search", async (req, res) => {
        const { keyword } = req.query;
        const query = 'SELECT * FROM book WHERE Title LIKE ?';  //模糊查询

    })


    // 导入cvs文件接口
    router.post('/import', async (req, res) => {
        const { file } = req.files;
        const filePath = file.path;
        const query = 'LOAD DATA LOCAL INFILE ? INTO TABLE book FIELDS TERMINATED BY "," LINES TERMINATED BY "\n" (Title, Author, ISBN, Publisher, PublishDate, Category, Stock)';
        const values = [filePath];
    })

    // 导出cvs文件接口
    router.get('/export', async (req, res) => {
        const workbook = new Excel.Workbook();
        const worksheet = workbook.addWorksheet('Books');

        worksheet.columns = [
            { header: '书名', key: 'title', width: 20 },
            { header: '作者', key: 'author', width: 20 },
            { header: 'ISBN', key: 'isbn', width: 15 },
            { header: '出版社', key: 'publisher', width: 30 },
            { header: '出版日期', key: 'publishDate', width: 20 },
            { header: '类别', key: 'category', width: 15 },
            { header: '库存', key: 'stock', width: 10 }
        ];


        // 导出工作簿
        workbook.xlsx.writeBuffer().then((buffer) => {
            res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            res.setHeader('Content-Disposition', 'attachment; filename=books.xlsx');
            res.end(buffer);
        }).catch((error) => {
            console.error('Error writing the Excel file: ', error);
            res.status(500).send('Error generating Excel file');
        });
    })


    return router;
}

module.exports = bookRouteModule;